﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Lamex.Library;

using System.Data.SqlClient;
using Lamex.Library.CommonClass;
using Lamex.Base;

namespace Lamex.mMES.UI
{
    public partial class frmMES_TableViewList : frmBaseChild
    {
        //private static string S_conStr = "Data Source=172.23.134.36;Initial Catalog=master;Integrated Security=False;user=sa;password=Sa@1050%;";
        private static string S_conStr = SqlConfiguration.CurrentConnectionString;

        public frmMES_TableViewList()
        {
            InitializeComponent();
        }

        private void frmMES_TableViewList_Load(object sender, EventArgs e)
        {

            #region 获取所有数据库的信息
            string sql = @" SELECT *
            FROM master..sysdatabases
            where dbid>6
            ORDER BY name";
            #endregion

            DataTable dt = ExcuteQuery(S_conStr, sql);

            if (S_conStr.Contains("172.23.134.36"))
            {
                this.treeView1.Nodes.Add("172.23.134.36");
            }
            else
            {
                this.treeView1.Nodes.Add("172.23.134.29");
            }

            foreach (DataRow dr in dt.Rows)
            {
                this.treeView1.Nodes[0].Nodes.Add(dr["name"].ToString());
            }

            this.treeView1.ExpandAll();
        }

        public static DataTable ExcuteQuery(string connectionString, string cmdText, List<SqlParameter> pars = null)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    if (pars != null && pars.Count > 0) cmd.Parameters.AddRange(pars.ToArray());
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        adp.Fill(dt);
                        return dt;
                    }
                }
            }
        }
        string sql = "";
        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            //Clipboard.SetDataObject("");
            if (e.Node.Level == 0) return;
            
            #region 获取数据库所有表字段信息的sql
            //string sql = @"

            //select
            //[表名]=c.Name,
            //[表说明]=isnull(f.[value],''),
            //[列序号]=a.Column_id,
            //[列名]=a.Name,
            //[列说明]=isnull(e.[value],''),
            //[数据库类型]=b.Name,
            //[类型]= case when b.Name = 'image' then 'byte[]'
            //when b.Name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
            //when b.Name in('tinyint','smallint','int','bigint') then 'int'
            //when b.Name in('datetime','smalldatetime') then 'DateTime'
            //when b.Name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
            //when b.Name ='bit' then 'bool' else b.name end ,
            //[标识]= case when is_identity=1 then '是' else '' end,
            //[主键]= case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name
            //join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)
            //then '是' else '' end,
            //[字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'
            //when b.Name='xml' then '2^31-1字节/2G'
            //else rtrim(a.[max_length]) end,
            //[长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1'
            //else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,
            //[小数位]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
            //[是否为空]=case when a.is_nullable=1 then '是' else '' end,
            //[默认值]=isnull(d.text,'')
            //from
            //sys.columns a
            //left join
            //sys.types b on a.user_type_id=b.user_type_id
            //inner join
            //sys.objects c on a.object_id=c.object_id and c.Type='U'
            //left join
            //syscomments d on a.default_object_id=d.ID
            //left join
            //sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
            //left join
            //sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ";
            #endregion

            if (e.Node.Level == 1)
            {
                sql = @"
            select m.* from {0}sys.sysobjects i
	     join 
	     (       select

                [表名]=c.Name,
                [表说明]=isnull(f.[value],''),
                [列序号]=a.Column_id,
                [列名]=a.Name,
                [列说明]=isnull(e.[value],''),
                [数据库类型]=b.schema_id,
                [类型]= case when b.Name = 'image' then 'byte[]'
                when b.Name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
                when b.Name in('tinyint','smallint','int','bigint') then 'int'
                when b.Name in('datetime','smalldatetime') then 'DateTime'
                when b.Name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
                when b.Name ='bit' then 'bool' else b.name end ,
                [标识]= case when is_identity=1 then '是' else '' end,
                [主键]= case when exists(select 1 from {1}sys.objects x join {2}sys.indexes y on x.Type=N'PK' and x.Name=y.Name
                join {3}sys.sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)
                then '是' else '' end,
                [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'
                when b.Name='xml' then '2^31-1字节/2G'
                else rtrim(a.[max_length]) end,
                [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1'
                else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,
                [小数位]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
                [是否为空]=case when a.is_nullable=1 then '是' else '' end,
                [默认值]=isnull(d.text,'')
			    from

                {4}sys.columns a 
                left join
               {5} sys.types b on a.user_type_id=b.user_type_id
                inner join
                {6}sys.objects c on a.object_id=c.object_id and c.Type='U'
                left join
                {7}sys.syscomments d on a.default_object_id=d.ID
                left join
                {8}sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
                left join
                {9}sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ) m on m.表名=i.name
			    where xtype IN('U','V') ";
                sql = string.Format(sql, e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".", e.Node.Text + ".");
              
                string strsql= sql+" order by i.Name";
                DataTable dt = ExcuteQuery(S_conStr.Replace("master", e.Node.Text), strsql);
                this.dataGridView1.DataSource = dt;

                if (dt.Rows.Count > 0)
                {
                    e.Node.Nodes.Clear();
                    DataRow[] aryDr = new DataRow[dt.Rows.Count];
                    dt.Rows.CopyTo(aryDr, 0);
                    List<string> listTableName = aryDr.Select(a => a["表名"].ToString()).Distinct().ToList();
                    listTableName.ForEach(a =>
                    {
                        e.Node.Nodes.Add(a, a);
                    });
                    e.Node.ExpandAll();
                }
            }

            if (e.Node.Level == 2)
            {
                
                
                //sql += " and i.Name=@TableName ";
                string strsql = sql + " and i.Name=@TableName ";;
                List<SqlParameter> listSqlParameter = new List<SqlParameter>()
                {
                    new SqlParameter("@TableName",e.Node.Text),
                };

                DataTable dt = ExcuteQuery(S_conStr.Replace("master", e.Node.Parent.Text), strsql, listSqlParameter);
                if (dt.Columns.Count > 0)
                {
                    if (dt.Rows.Count > 0)
                    {
                        e.Node.Tag = dt.Rows[0]["表说明"].ToString();
                    }
                    dt.Columns.Remove("表名");
                    dt.Columns.Remove("表说明");
                }
                this.dataGridView1.DataSource = dt;
            }
        }

        private void frmMES_TableViewList_Resize(object sender, EventArgs e)
        {
            //dataGridView1.Width = this.Width - treeView1.Width;
            //treeView1.Height = this.Height - button1.Height;
            //button1.Location = new Point(0, this.Height - button1.Height);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MdiTools.OpenIE(@"\\Tx-fs10\tx-fs1\LAMEX\Tools\DBCHM v1.7.3.8\DBCHM.exe");
        }
    }
}